﻿using EA.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EA.DAO
{
    public class CustomerDA:ObjectDA
    {
        public CustomerDA(String connection_string)
            : base(connection_string)
        {

        }

        public DataTable GetCustomerByProductID(int ID)
        {
            String query = "SELECT Customer.* FROM Customer INNER JOIN PRODUCT ON Product.SellerID = Customer.ID  WHERE PRODUCT.ID=@ID";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@ID", ID);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public DataTable GetCustomerByID(int ID)
        {
            String query = "SELECT * FROM Customer WHERE ID=@ID";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@ID",ID);
            DataTable table = new DataTable();
            this.Fill(query,param,table);
            return table;
        }

        public DataTable GetCustomerByEmail(String email)
        {
            String query = "SELECT * FROM Customer WHERE Email=@email";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@email", email);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public Boolean DeleteCustomer(int ID)
        {
            String query = "DELETE Customer WHERE ID=@ID";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@ID", ID);
            return this.ExecuteNonQuery(query, param);
        }

        public Boolean UpdateCustomer(CustomerTO customer)
        {
            String query = "UPDATE Customer SET Sellable=@sellable,Password=@password,FullName=@fullName,Phone=@phone,Address=@address WHERE ID=@ID";
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("ID", customer.ID);
            param[1] = new SqlParameter("fullName", customer.FullName);
            param[2] = new SqlParameter("password", customer.Password);
            param[3] = new SqlParameter("phone", customer.Phone);
            param[4] = new SqlParameter("address", customer.Address);
            param[5] = new SqlParameter("sellable", customer.Sellable);
            return this.ExecuteNonQuery(query, param);
        }

        public Boolean InsertCustomer(CustomerTO customer)
        {
            String query = "INSERT INTO Customer(Sellable,Email,Password,FullName,Phone,Address)" +
                                 " VALUES(@Sellable,@Email,@Password,@FullName,@Phone,@Address)";
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@Sellable", customer.Sellable);
            param[1] = new SqlParameter("@Email", customer.Email);
            param[2] = new SqlParameter("@Password", customer.Password);
            param[3] = new SqlParameter("@FullName", customer.FullName);
            param[4] = new SqlParameter("@Phone", customer.Phone);
            param[5] = new SqlParameter("@Address", customer.Address);
            return this.ExecuteNonQuery(query, param);
        }

        public DataTable GetListCustomer(int index1, int index2)
        {
            String query = "SELECT  * "
                            + " FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY ID ) AS IndexRow, * "
                            + " FROM Customer  ) AS RowConstrainedResult "
                            + " WHERE   IndexRow >= @Index1 AND IndexRow <= @Index2 "
                            + " ORDER BY IndexRow ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@Index1", index1);
            param[1] = new SqlParameter("@Index2", index2);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public DataTable GetListCustomer()
        {
            String query = "SELECT  * FROM Customer";
            DataTable table = new DataTable();
            this.Fill(query, null, table);
            return table;
        }

        public DataTable GetTotalCustomer()
        {
            String query = "SELECT  COUNT(*) AS TOTAL FROM Customer";
            DataTable table = new DataTable();
            this.Fill(query, null, table);
            return table;
        }
    }
}
